<?php
namespace DB;
use Config\Base as Config;
use Exception;
use Models\Album;
use Models\Article;
use Models\ArticleContent;
use Models\Attachment;
use Models\Photo;

/**
 * @author Evgeny Baranov <evgeny@baranov.eu>
 *
 */
class ArticleService {

    /**
     * @param integer $page
     * @param integer $num
     * @param integer $group
     * @return array
     */
    public function getList($page = 1, $num = 10, $group = null) {
        $query = 'SELECT SQL_CALC_FOUND_ROWS * FROM `article` a
			JOIN `article_content` ac on ac.`article_id` = a.`id` AND ac.language_code = :language_code
			WHERE (:group IS NULL OR a.`group_id` = :group) AND (:all OR a.`status_id` = 1) ORDER BY a.`created` DESC LIMIT :offset,:num';

        $bind = array(
            'num' => intval($num),
            'offset' => $num * ($page - 1),
            'language_code' => \App::getLang(),
            'all' => ($u = \App::getCurrentUser()) && $u->isAdmin(),
            'group' => $group
        );
        $result = DAO::getInstance()->query($query, $bind);

        $count = DAO::getInstance()->query('SELECT FOUND_ROWS() as count');
        $count = $count[0]['count'];
        $us = new UserService();
        foreach ($result as &$r) {
            $r['author'] = $us->getUser($r['author_id']);
            $r = new Article($r);
            $this->getArticleAlbum($r);
        }

        // TODO: create collection class
        return array(
            'total' => $count,
            'page' => $page,
            'num' => $num,
            'pages' => ceil($count / $num),
            'list' => $result
        );
    }

    /**
     * @param null $group
     * @param integer $num
     * @throws Exception
     * @return array
     * @internal param string $language
     */
    public function getLatest($group = null, $num = 10) {
        $query = 'SELECT * FROM `article` a
			JOIN `article_content` ac on ac.`article_id` = a.`id` AND ac.language_code = :language_code
			WHERE (:group IS NULL OR a.`group_id` = :group) AND (:all OR a.`status_id` = 1) ORDER BY a.`created` DESC LIMIT :num';
        $bind = array(
            'num' => $num,
            'language_code' => \App::getLang(),
            'all' => ($u = \App::getCurrentUser()) && $u->isAdmin(),
            'group' => $group
        );
        $result = DAO::getInstance()->query($query, $bind);

        if (count($result) == 0) {
            throw new Exception("No latest articles.");
        }

        $us = new UserService();
        foreach ($result as &$article) {
            $article['author'] = $us->getUser($article['author_id']);
            $article = new Article($article);
            $this->getArticleAlbum($article);
        }

        return $result;
    }

    /**
     * @param string $path
     * @param null $from
     * @throws Exception
     * @return Article
     * @internal param string $language
     */
    public function findByRoute($path, $from = null) {
        $bind = array(
            'path' => urldecode($path),
            'all' => ($u = \App::getCurrentUser()) && $u->isAdmin(),
            'lang' => \App::getLang(),
            'default_lang' => !empty(Config::instance()->lang->default) ? Config::instance()->lang->default : 'en'
        );

        $query = 'SELECT * FROM (
            SELECT * FROM `article` a
                JOIN `article_content` ac on ac.`article_id` = a.`id` AND ac.language_code = :lang
                WHERE (:all OR a.`status_id` = 1) AND ac.`path` = :path
            UNION
            SELECT * FROM `article` a
                JOIN `article_content` ac on ac.`article_id` = a.`id` AND ac.language_code = :default_lang
                WHERE (:all OR a.`status_id` = 1) AND ac.`path` = :path
            UNION
            SELECT a.*, ac.* FROM `article` a
                JOIN `article_content` ac2 on ac2.`article_id` = a.`id` AND ac2.`path` = :path AND ac2.language_code <> :lang
                JOIN `article_content` ac on ac.`article_id` = ac2.`article_id` AND ac.language_code = :lang
                WHERE (:all OR a.`status_id` = 1)
          ) s LIMIT 1';


        $article = DAO::getInstance()->query($query, $bind);

        if (count($article) == 0) {
            throw new Exception("Article not found on path: {$path}");
        }
        $us = new UserService();
        $article = array_pop($article);
        $article['author'] = $us->getUser($article['author_id']);
        $article = new Article($article);
        $this->getArticleAlbum($article);

        return $article;
    }

    /**
     * @param integer $id
     * @param string|null $lang
     * @throws Exception
     * @return Article[]
     */
    public function getById($id, $lang = null) {
        if ($lang == null) {
            $query = 'SELECT * FROM `article` a
				JOIN `article_content` ac on ac.`article_id` = a.`id`
				WHERE a.`id` = :id
				AND (:lang IS NULL OR ac.language_code = :lang OR ac.language_code = :default_lang)';
        } else {
            $query = 'SELECT * FROM(SELECT * FROM `article` a1
				JOIN `article_content` ac1 ON ac1.`article_id` = a1.`id`
				WHERE
					a1.`id` = :id AND
					ac1.`language_code` = :lang
				UNION
				SELECT * FROM `article` a2
				JOIN `article_content` ac2 ON ac2.`article_id` = a2.`id`
				WHERE
					a2.`id` = :id AND
					ac2.`language_code` = :default_lang
				UNION
				SELECT * FROM `article` a3
				JOIN `article_content` ac3 ON ac3.`article_id` = a3.`id`
				WHERE
					a3.`id` = :id) a LIMIT 1
				';
        }

        $bind = array(
            'id' => intval($id),
            'lang' => $lang,
            'default_lang' => Config::instance()->lang->default
        );
        $result = DAO::getInstance()->query($query, $bind);


        if (count($result) == 0) {
            throw new Exception("Article ID#{$id} ({$lang})  not found.", 404);
        }

        $us = new UserService();

        foreach ($result as &$article) {
            $article['author'] = $us->getUser($article['author_id']);
            $article = new Article($article);
            $this->getArticleAlbum($article);
        }
        return $result;
    }

    /**
     * @param Article $article
     * @return Article
     */
    public function saveArticle(Article $article) {
        if ($article->haveAlbum()) {
            if (!$article->getAlbum()) {
                $fb = new FacebookService();
                $album = $fb->getAlbum($article->getAlbumId());
                $this->saveArticleAlbum($album);
                $article->fromArray(array(
                    'album' => $album
                ));
            }
            $this->saveArticleAlbum($article->getAlbum());
        }

        if ($article->getId()) {
            $this->updateArticle($article);
        } else {
            $this->createArticle($article);
        }

        $this->saveArticleContent($article->getContent());

        return $article;
    }

    /**
     * @param Article $article
     * @return Article
     */
    protected function updateArticle(Article $article) {
        $bind = array(
            'author_id' => $article->getAuthorId(),
            'status_id' => $article->getStatusId(),
            'group_id' => $article->getGroupId(),
            'album_id' => $article->getAlbumId() ? $article->getAlbumId() : null,
            'id' => $article->getId()
        );
        $query = 'UPDATE `article` SET '
            . '`author_id` = :author_id, `status_id` = :status_id,  `group_id` = :group_id, `album_id` = :album_id '
            . 'WHERE id = :id';

        DAO::getInstance()->exec($query, $bind);

        return $article;
    }

    /**
     * @param Article $data
     * @return Article
     */
    protected function createArticle(Article $data) {
        $query = 'INSERT INTO `article` '
            . '(`author_id`, `group_id`, `status_id`, `created`, `updated`, `album_id`) VALUES '
            . '(:author_id,  :group_id,  :status_id,   now(),     now(),    :album_id)';

        $bind = array(
            'author_id' => \App::getCurrentUser()->getId(),
            'status_id' => Article::ACTIVE,
            'group_id' => $data->getGroupId(),
            'album_id' => $data->getAlbumId() ? $data->getAlbumId() : null
        );

        DAO::getInstance()->exec($query, $bind);
        $id = DAO::getInstance()->lastInsertId();

        $data->fromArray(array('id' => $id));
        $data->getContent()->fromArray(array('article_id' => $id));

        return $data;
    }

    /**
     * @param ArticleContent $data
     * @return ArticleContent
     */
    protected function saveArticleContent(ArticleContent $data) {
        $query = 'INSERT INTO `article_content` (`article_id`, `language_code`, `path`, `title`, `annotation`, `content`)'
            . ' VALUES '
            . '(:article_id, :language_code, :path, :title, :annotation, :content) '
            . 'ON DUPLICATE KEY UPDATE article_id = :article_id, language_code = :language_code,
				path = :path,
				title = :title,
				content = :content,
				annotation = :annotation';

        DAO::getInstance()->exec($query, $data->toArray());

        return $data;
    }

    /**
     * @param Album $album
     * @return Album
     */
    protected function saveArticleAlbum(Album $album) {
        $query = 'INSERT INTO `album` (`object_id`, `owner`, `name`, `link`, `photo_count`, `created`, `updated`)'
            . ' VALUES '
            . '(:object_id, :owner, :name, :link, :photo_count, FROM_UNIXTIME(:created), FROM_UNIXTIME(:updated)) '
            . 'ON DUPLICATE KEY UPDATE '
            . ' `object_id` = :object_id,
				`owner` = :owner, 
				`name` = :name, 
				`link` = :link, 
				`photo_count` = :photo_count, 
				`created` = FROM_UNIXTIME(:created), 
				`updated` = FROM_UNIXTIME(:updated)';

        DAO::getInstance()->exec($query, $album->toArray());

        return $this->saveAlbumPhotos($album);
    }

    /**
     * @param Album $album
     * @return Album
     */
    protected function saveAlbumPhotos(Album $album) {
        if ($album->getPhotoCount() != count($album->getPhotos())) {
            $fb = new FacebookService();
            $photos = $fb->getAlbumPhotos($album->getId());
            $album->fromArray(array(
                'photos' => $photos
            ));
        }
        foreach ($album->getPhotos() as $p) {
            $query = 'INSERT INTO `photo` (`object_id`, `position`, `owner`, `caption`, `link`, `images`, `album_object_id`, `created`, `updated`)'
                . ' VALUES '
                . '(:object_id, :position, :owner, :caption, :link, :images, :album_object_id, FROM_UNIXTIME(:created), FROM_UNIXTIME(:updated)) '
                . 'ON DUPLICATE KEY UPDATE '
                . ' `object_id` = :object_id,
					`position` = :position,
					`owner` = :owner, 
					`caption` = :caption, 
					`link` = :link, 
					`images` = :images, 
					`album_object_id` = :album_object_id,
					`created` = FROM_UNIXTIME(:created), 
					`updated` = FROM_UNIXTIME(:updated)';


            DAO::getInstance()->exec($query, $p->toArray());
        }

        return $album;
    }

    /**
     * @param Article $data
     * @return Article
     */
    public function hideArticle(Article $data) {
        $query = "UPDATE `article` SET `status_id` = :status WHERE `id` = :id";
        $bind = array(
            'status' => $data::HIDDEN,
            'id' => $data->getId()
        );

        DAO::getInstance()->exec($query, $bind);

        return $data;
    }

    /**
     * @param Article $article
     * @return Article
     */
    public function deleteArticle(Article $article) {
        $query = "UPDATE `article` SET `status_id` = :status WHERE `id` = :id";
        $bind = array(
            'status' => $article::DELETED,
            'id' => $article->getId()
        );

        DAO::getInstance()->exec($query, $bind);

        return $article;
    }

    /**
     * @param Article $article
     * @return boolean
     */
    public function log(Article $article) {
        $query = 'INSERT IGNORE into  `article_log` '
            . '(`article_id`, `language_code`, `session`, `user_id`, `ts`)'
            . ' VALUES '
            . '(:article_id, :language_code, :session, :user_id, NOW())';
        $bind = array(
            'article_id' => $article->getId(),
            'language_code' => $article->getContent()->getLanguageCode(),
            'session' => session_id(),
            'user_id' => ($u = \App::getCurrentUser()) ? $u->getId() : null
        );

        DAO::getInstance()->exec($query, $bind);

        return true;
    }

    /**
     * @param Article $article
     * @return Article
     */
    public function getArticleAlbum(Article &$article) {
        if (!$article->haveAlbum()) {
            return $article;
        }

        $query = "SELECT "
            . " * "
            . "FROM `album` a "
            . "WHERE a.`object_id` = :object_id";
        $bind = array(
            'object_id' => $article->getAlbumId()
        );

        $album = DAO::getInstance()->query($query, $bind);

        if (count($album) > 0) {
            $album = new Album(array_shift($album));
            if ($album->havePhotos()) {
                $this->getAlbumPhotos($album);
            }
            $article->fromArray(array(
                'album' => $album
            ));
        } else {
            $fb = new FacebookService();
            $album = $fb->getAlbum($article->getAlbumId());
            $this->saveArticleAlbum($album);
            $article->fromArray(array(
                'album' => $album
            ));
        }

        return $article;
    }

    /**
     * @param Album $album
     * @return Album
     */
    public function getAlbumPhotos(Album &$album) {
        $query = "SELECT "
            . " * "
            . "FROM `photo` p "
            . "WHERE p.`album_object_id` = :album_object_id";
        $bind = array(
            'album_object_id' => $album->getId()
        );

        $photos = DAO::getInstance()->query($query, $bind);

        foreach ($photos as &$p) {
            $p = new Photo($p);
        }

        $album->fromArray(array(
            'photos' => $photos
        ));

        return $album;
    }

    /**
     * @param Attachment $att
     * @return Attachment
     */
    public function saveAttachment(Attachment &$att) {
        $bind = $att->toArray();
        unset($bind['size']);
        unset($bind['article_id']);
        unset($bind['type']);

        if ($att->getId() > 0) {
            $query = 'UPDATE`attachment` SET '
                . '`name` = :name, `description` = :description, `mime` = :mime, `updated` = NOW() '
                . 'WHERE `id` = :id';
            //unset($bind['data']);
        } else {
            $bind['data'] = $att->getData();
            unset($bind['id']);
            $query = 'INSERT INTO `attachment` '
                . '(`name`, `description`, `mime`, `data`, `created`, `updated`)'
                . ' VALUES '
                . '(:name, :description, :mime, :data, NOW(), NOW())';
        }

        DAO::getInstance()->exec($query, $bind);

        if (!$att->getId()) {
            $att->fromArray(array(
                'id' => DAO::getInstance()->lastInsertId()
            ));
        }

        return $att;
    }

    /**
     * @param integer $id
     * @throws Exception
     * @return Attachment
     */
    public function getAttachment($id) {
        $query = "SELECT "
            . "a.`id`, a.`name`, a.`description`, a.`mime`, OCTET_LENGTH(a.`data`) as `size`, `data`, aa.`type`, aa.`article_id` "
            . "FROM `attachment` a "
            . "JOIN `article_attachment` aa on a.`id` = aa.`attachment_id` "
            . "WHERE a.`id` = :id";

        $bind = array(
            'id' => $id
        );

        $banner = DAO::getInstance()->query($query, $bind);

        if (count($banner) > 0) {
            $banner = new Attachment(array_shift($banner));
        } else {
            throw new Exception("Attachment #{$id} not found", 404);
        }

        return $banner;
    }

    /**
     * @param Attachment $att
     * @return boolean
     */
    public function removeAttachment(Attachment &$att) {
        $query = "DELETE FROM `attachment` WHERE `id` = :id";
        $bind = array(
            'id' => $att->getId()
        );

        DAO::getInstance()->exec($query, $bind);

        return true;
    }
}